package BookManage;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

//此类主要提供Book表的增删改查方法。
public class DaoBook {
    //新增图书
    public void addBook(Book book){
        Connection connection=null;
        PreparedStatement statement=null;
        try {
            //和数据库进行连接
            connection=databaseOperation.getConnection();
            //构造SQL语句
            String sql="insert into book values(null,?,?,?,?,0)";
            //和数据库服务端进行交互
            statement=connection.prepareStatement(sql);
            statement.setString(1,book.getName());
            statement.setString(2,book.getAuthor());
            statement.setInt(3,book.getMoney());
            statement.setString(4,book.getType());
            //执行SQL语句
            int ret=statement.executeUpdate();
            //看影响行数，判断是否插入成功。
            if(ret!=1){
                System.out.println("新增书籍失败");
            }else{
                System.out.println("新增书籍成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            //不管是否报错都要执行的操作,进行回收
            databaseOperation.close(connection,statement,null);
        }
    }
    //查看所有图书
    public List<Book> FindBooks(){
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        List<Book> list=new ArrayList<>();
        try {
            //与数据库建立连接
            connection=databaseOperation.getConnection();
            //构造SQL语句
            String sql="select * from book";
            statement= connection.prepareStatement(sql);
            //执行SQL语句
            resultSet=statement.getResultSet();
            //循环遍历结果集
            while(resultSet.next()){
                Book book=new Book();
                //每次循环是每一行的信息，再从中获取每一列的字段信息
                book.setBookId(resultSet.getInt("bookId"));
                book.setName(resultSet.getString("name"));
                book.setAuthor(resultSet.getString("author"));
                book.setMoney(resultSet.getInt("money"));
                book.setType(resultSet.getString("type"));
                book.setState(resultSet.getInt("state")==1);
                list.add(book);
            }
        } catch (SQLException e){
            e.printStackTrace();
        }finally {
            //回收
            databaseOperation.close(connection,statement,resultSet);
        }
        return list;
    }
    //按书名查找一本书
    public List<Book> FindBooksByName(String name){
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        List<Book> list=new ArrayList<>();
        try{
            //建立连接
            connection=databaseOperation.getConnection();
            //构造SQL语句
            String sql="select * from book where name=?";
            statement=connection.prepareStatement(sql);
            statement.setString(1,name);
            //执行SQL语句
            resultSet=statement.executeQuery();
            //循环遍历结果集
            while(resultSet.next()){
                //得到每一行的每一列的字段信息
                Book book=new Book();
                book.setBookId(resultSet.getInt("bookId"));
                book.setName(resultSet.getString("name"));
                book.setAuthor(resultSet.getString("author"));
                book.setMoney(resultSet.getInt("money"));
                book.setType(resultSet.getString("type"));
                book.setState(resultSet.getInt("state")==1);
                list.add(book);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            //回收
            databaseOperation.close(connection,statement,resultSet);
        }
        return null;
    }
    //删除一本书
    public boolean delete(int bookId){
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        try{
            connection=databaseOperation.getConnection();
            String sql="delete from book where bookId=?";
            statement=connection.prepareStatement(sql);
            statement.setInt(1,bookId);
            int ret=statement.executeUpdate();
            if(ret!=1){
                System.out.println("删除失败");
                return false;
            }else{
                System.out.println("删除成功");
                return true;
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            databaseOperation.close(connection,statement,resultSet);
        }
        return false;
    }
    //借书
    public boolean borrowBook(int bookId){
        Connection connection=null;
        PreparedStatement statement=null;
        PreparedStatement statement2=null;
        ResultSet resultSet=null;
        try{
            connection=databaseOperation.getConnection();
            String sql="select * from book where bookId=?";
            statement=connection.prepareStatement(sql);
            statement.setInt(1,bookId);
            resultSet=statement.executeQuery();
            if(resultSet.next()){
                boolean bool=(resultSet.getInt("state")==1);
                if(bool){
                    System.out.println("书已借出");
                    return false;
                }
            }else{
                System.out.println("没有此书");
                return false;
            }
            sql="update book set state=1 where bookId=?";
            statement2=connection.prepareStatement(sql);
            statement2.setInt(1,bookId);
            int ret=statement2.executeUpdate();
            if(ret!=1){
                System.out.println("借书失败");
                return false;
            }else{
                return true;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally{
//            databaseOperation.close(connection,statement,resultSet);
            if(resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement!=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement2!=null){
                try {
                    statement2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return false;
    }
    //还书
    public boolean returnBook(int bookId){
        Connection connection=null;
        PreparedStatement statement=null;
        PreparedStatement statement2=null;
        ResultSet resultSet=null;
        try{
            connection=databaseOperation.getConnection();
            String sql="select * from book where bookId=?";
            statement=connection.prepareStatement(sql);
            statement.setInt(1,bookId);
            resultSet=statement.executeQuery();
            if(resultSet.next()){
                boolean bool=(resultSet.getInt("state")==1);
                if(!bool){
                    System.out.println("书已被归还");
                    return false;
                }
            }else{
                System.out.println("没有此书");
                return false;
            }
            sql="update book set state=0 where bookId=?";
            statement2=connection.prepareStatement(sql);
            statement2.setInt(1,bookId);
            int ret=statement2.executeUpdate();
            if(ret!=1){
                System.out.println("借书失败");
                return false;
            }else{
                return true;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally{
//            databaseOperation.close(connection,statement,resultSet);
            if(resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement!=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement2!=null){
                try {
                    statement2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return false;
    }
}
